#抓取39.net 药品查询
from bs4 import BeautifulSoup
import requests
import collections
import pymssql
import re
import datetime
import queue
import time
import threading
# http://ypk.39.net/AllCategory
# 采集39net上的药品信息
class collect39net(object):
    _htmlurl='http://ypk.39.net/AllCategory'
    _server=''
    _user=''
    _dbname=''    
    _password=''
    _showurl=False #打印 请求地址信息
    _lst=queue.Queue()#队列
    _reqipport='' #代理IP:端口
    _reqid=0
    _useProxy=False #启用代理
    _showDb=False #显示数据库信息
    _lockr=threading.Lock()#全局锁
    _lockw=threading.Lock()#全局锁
    # 默认构造方法
    def __init__(self,server:str,user:str,pwd:str,dbname:str,showurl=False,useProxy=False,showDb=False):
        self._server=server
        self._user=user
        self._password=pwd
        self._dbname=dbname        
        self._showurl=showurl
        self._useProxy=useProxy
        self._showDb=showDb
    def collectAllCategory(self,tpcode=0):
        r'''抓取 药品查询 全部分类'''
        try:         
            html=self.__requestHtml(self._htmlurl,tpcode)
            bf=BeautifulSoup(html,'lxml')
            div=bf.find_all('div',class_='classification-box') # 包含  按疾病找药品   按分类找药品  保健品 中药材 家用器械
            for d in div:
                ul=d.find_all('ul') #链接
                mnane=ul[0].text  #分类名称                
                li=ul[1].find_all('li') #子项    
                for l in li[1:]:
                    stro=l.find_all('strong')[0].find_all('a')[0] #子项名称                    
                    mx=l.find_all('p')[0].find_all('a')
                    for m in mx:                        
                        turl= stro['href'] if stro['href'].find('http://ypk.39.net')>=0 else ('http://ypk.39.net'+stro['href'])
                        self.__save39Category(mnane,stro.text,turl,m.text,'http://ypk.39.net'+m['href'])
        except Exception as e:
            print('请求出错')
            print(e)
            if self._useProxy:
                print('代理IP地址无效了')
                if tpcode==0:
                    self.collectAllCategory(1)
                else:
                    self.__changeProxyIp() 
                    self.collectAllCategory(0)
    # 读取数据
    def __readMssqlData(self,sql:str,params:tuple):
        self._lockr.acquire()  # 修改数据前加锁
        conn=pymssql.connect(self._server,self._user,self._password,self._dbname) #连接数据库 (服务器地址,登录数据库账号,登录数据库账号密码,指定数据库)
        cursor=conn.cursor() #获取光标
        try:
            cursor.execute(sql,params) #执行SQL        
            rows=cursor.fetchall() #获取所有数据
            conn.commit()
            self._lockr.release()  # 修改数据前解锁
            return rows            
        except Exception as e:        
            self._lockr.release()  # 修改数据前解锁
            print(e)   #输出异常            
            return []
        finally:
            conn.close()  # 数据库操作完成后要关闭连接
    
    # 保存数据  
    def __writeToMssql(self,sql:str,params:list):
        self._lockw.acquire()  # 修改数据前加锁
        conn=pymssql.connect(self._server,self._user,self._password,self._dbname) #连接数据库 (服务器地址,登录数据库账号,登录数据库账号密码,指定数据库)
        cursor=conn.cursor() #获取光标
        try:
            cursor.executemany(sql,params)
            conn.commit()
            self._lockw.release()  # 修改数据前解锁
        except Exception as e:
            self._lockw.release()  # 修改数据前解锁
            print('保存出现异常')
            print(e)   #输出异常
        finally:
            conn.close()  # 数据库操作完成后要关闭连接 
    
    # 全部分类保存到数据
    def __save39Category(self,mname:str,tname:str,turl:str,iname:str,iurl:str):
        r'''全部分类保存到数据
        :param mname 模块名称
        :param tname 类型名称
        :param turl 类型链接
        :param iname 子项名称
        :param iurl 子项链接        
        '''        
        # print('mname:%s,tname:%s,turl:%s,iname:%s,iurl:%s'%(mname,tname,turl,iname,iurl))
        rows=self.__readMssqlData('select * from Drag39Type WHERE TypeUrl=%s AND ItemUrl=%s ',(turl,iurl))
        if len(rows)==0:
            self.__writeToMssql('insert into Drag39Type(ModualName,TypeName,TypeUrl,ItemName,ItemUrl)values(%s,%s,%s,%s,%s)',[(mname,tname,turl,iname,iurl)])
            
    # 按分类找药品
    def __getFindByType(self):
        r'''按分类找药品'''
        _sql='select distinct ModualName,TypeName,TypeUrl from Drag39Type where ModualName=%s '
        return self.__readMssqlData(_sql,('按分类找药品'))
    
    # 按疾病找药品
    def __getFindByIllness(self):
        r'''按疾病找药品'''
        _sql='select distinct ModualName,TypeName,TypeUrl from Drag39Type where ModualName=%s '
        return self.__readMssqlData(_sql,('按疾病找药品'))
    
    # 中药材 【涉及分页查询】
    def __getFindByMedicine(self):
        r'''中药材'''
        _sql='select distinct ModualName,TypeName,TypeUrl from Drag39Type where ModualName=%s '
        return self.__readMssqlData(_sql,('中药材'))

    # 处理疾病Url 并保存数据
    def  __requestCommonUrl(self,row:tuple):
        r'''处理疾病Url'''
        try:
            html=self.__requestHtml(row[2],0)
            bf=BeautifulSoup(html,'lxml')
            ul=bf.find_all('ul',class_='letter-fl') #包含 A-Z
            if len(ul)>0:
                li=ul[0].find_all('li')
                for l in li:
                    tg=l.find_all('a')
                    for a in tg:
                         self.__save39Category(row[0],row[1],row[2],a.text,'http://ypk.39.net'a['href'])
        except Exception as e:
            print('网页请求异常')
            print(e)            
    
    # 处理疾病Url 
    def DealIllnessUrl(self):
        r'''处理疾病Url'''
        rows=self.__getFindByIllness()        
        for r in rows:
            self.__requestCommonUrl(r)
            time.sleep(2)
    
    # 处理分类Url 
    def DealTypeUrl(self):
        r'''处理分类Url'''
        rows=self.__getFindByType()        
        for r in rows:
            self.__requestCommonUrl(r)
            time.sleep(2)
    
    # 公共请求Url 获取Html
    def __requestHtml(self,htmlurl,tpcode):
        r'''公共请求Url 获取Html
        :param htmlurl 请求地址
        :param tpcode 0 http   1 https 
        '''
        proxy={'http':self._reqipport}
        if tpcode==1:
            proxy={'https':self._reqipport}
        if self._showurl:
            print(htmlurl)
            if self._useProxy:
                print(proxy)
        req= requests.get(url=htmlurl,proxies=proxy) if self._useProxy else requests.get(url=htmlurl)
        return req.text
    
    # 加载代理IP
    def __loadProxyIp(self):
        rows=self.__readMssqlData('select Id,IpAddress,Port from [DaiLiIpAddress] where [IsValid]=%d ',(1))
        for r in rows:
            self._lst.put({'id':r[0],'ip':r[1],'port':str(r[2])})
        ip=self._lst.get()    
        self._reqipport=ip['ip']+':'+ip['port']
        self._reqid=ip['id']
        
    # 切换代理IP 
    def __changeProxyIp(self):
        self.__writeToMssql('update [dbo].[DaiLiIpAddress] set IsValid=0 where Id=%d',[(self._reqid)])
        ip=self._lst.get()
        self._reqipport=ip['ip']+':'+ip['port']
        self._reqid=ip['id']
        
#######################数据库信息################################
# 数据库IP地址
server = 'localhost'
# 数据库用户名
user = 'sa'
# 数据库密码
password = '123456'
# 数据库名
dbname = 'myTest'
c39=collect39net(server,user,password,dbname,True)
# c39.collectAllCategory()
# c39.DealIllnessUrl()
# c39.DealTypeUrl()